﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;
using System.Collections;
using System.Data.Objects;
using System.Data.Objects.SqlClient;


namespace XBoshiGFS.reports
{
    public partial class WaterBillPublishReportForm : Form
    {
        XBoshiGFSModelContainer context = new XBoshiGFSModelContainer();
        IQueryable  orgSql;
        String strGroupID;


        public WaterBillPublishReportForm()
        {
            InitializeComponent();

            cmbStation.DataSource = (from p in context.StationSet select p);

            //灌季
            cmbWaterSeason.DataSource = (from p in context.WaterSeasonSet orderby p.strYear descending ,p.strSeason descending  select p);

        }
 
        private void crystalReportViewer1_Load(object sender, EventArgs e)
        {
                strGroupID = "-1";

                try
                {
                 //orgSql = 
                 //            from  t in context.PeopleSet
                 //            join o in context.GroupSet on t.GroupId equals o.Id  
                 //            join p in context.VillageSet on o.VillageId equals p.Id
                 //            join q in context.WaterCourseSet on p.WaterCourseId equals q.Id
                 //            join r in context.SegmentCourseSet on q.SegmentCourseId equals r.Id
                 //            join s in context.StationSet on r.StationId equals s.Id
                 //            where p.Id == this.strGroupID
                 //            select new  {
                 //                t.strCode, t.strName ,
                 //                strGropId =p.Id,strGroupName =p.strName ,strGroupCode =p.strCode ,
                 //                strVillageCode =o.strCode ,strVillageName =o.strName ,
                 //                strWaterCourseCode =q.strCode ,strWaterCourseName  =q.strName ,
                 //                strSegmentCode =r.strCode ,strSegmentName =r.strName ,
                 //                strStationCode =s.strCode ,strStationName=s.strName 
                 //             };

                 //var orgSql = GetQuerySql("-1");


                 WaterPublishReportData wprd = new WaterPublishReportData();
                 //wprd.decThisArrears =new Decimal(234567.8);
                 //wprd.decLastArrears = 0;
                 //wprd.decUseArea  = new Decimal(4567.8);
                 //wprd.decWaterBill  = double.Parse("1111.32") ;
                 //wprd.decWaterCap  = 4567.8M;

                 WaterBillPublishReport1.SetDataSource(new List<WaterPublishReportData>() { wprd});
         }
                catch (Exception ex) {

                    var a = ex.InnerException;
                } 
        }


        private void cmbStation_SelectedIndexChanged(object sender, EventArgs e)
        {
            var strID = ((ComboBox)sender).SelectedValue;
            cmbSegment.DataSource = (from p in context.SegmentCourseSet where p.StationId == strID orderby p.strName select p);
        }

        private void cmbSegment_SelectedIndexChanged(object sender, EventArgs e)
        {
            var strID = ((ComboBox)sender).SelectedValue;
            cmbWaterCourse.DataSource = (from p in context.WaterCourseSet where p.SegmentCourseId == strID orderby p.strName select p);

        }

        private void cmbWaterCourse_SelectedIndexChanged(object sender, EventArgs e)
        {
            var strID = ((ComboBox)sender).SelectedValue;
            cmbVillage.DataSource = (from p in context.VillageSet where p.WaterCourseId == strID orderby p.strName select p);

        }

        private void cmbVillage_SelectedIndexChanged(object sender, EventArgs e)
        {
            var strID = ((ComboBox)sender).SelectedValue;
            cmbGroup.DataSource = (from p in context.GroupSet where p.VillageId == strID orderby p.strName select p);

        }

        private void btnQuery_Click(object sender, EventArgs e)
        {
            try
            {
                strGroupID = (String)cmbGroup.SelectedValue;
                var sql = GetQuerySql(strGroupID);

                WaterBillPublishReport1.SetDataSource(sql);
                WaterBillPublishReport1.Refresh();
                crystalReportViewer1.RefreshReport();     
            }catch(Exception ex){
            
            } 
        }

        private IQueryable GetQuerySql(String strGroupID)
        {
            String strSeasonID="";
            String feeID = "";
            try
            {
                  WaterSeasonSet season = ((WaterSeasonSet)(cmbWaterSeason.SelectedItem));
                  //SeasonName = season.strName;
                  strSeasonID = season.Id;
                  feeID = season.FeeStandardSetId;
                 // PriceAll = String.Format("{0}", (from p in context.FeeStandardSet where p.Id == season.FeeStandardSetId select p.PriceAll).FirstOrDefault());
            }
            catch { 
            
            
            }
           var orgSql =
            from u in context.WaterUsageSet 
            join t in context.PeopleSet on  u.PeopleId  equals t.Id 
            join o in context.GroupSet on t.GroupId equals o.Id
            join p in context.VillageSet on o.VillageId equals p.Id
            join q in context.WaterCourseSet on p.WaterCourseId equals q.Id
            join r in context.SegmentCourseSet on q.SegmentCourseId equals r.Id
            join s in context.StationSet on r.StationId equals s.Id
            join fee in context.FeeStandardSet on u.FeeStandardId  equals fee.Id 
            join season in context.WaterSeasonSet on u.WaterSeasonId  equals season.Id 
            where o.Id == this.strGroupID  && season.Id == strSeasonID && u.WaterCap >0
            orderby t.strCode 
            select new  //WaterPublishReportData 
            {
                strPeopleID = t.Id,
                strPeopleCode= t.strCode,
                strPeopleName=t.strName,
                strGropId = p.Id,
                strGroupName = o.strName,
                strGroupCode = o.strCode,
                strVillageCode = p.strCode,
                strVillageName = p.strName,
                strWaterCourseCode = q.strCode,
                strWaterCourseName = q.strName,
                strSegmentCode = r.strCode,
                strSegmentName = r.strName,
                strStationCode = s.strCode,
                strStationName = s.strName,
                strGroupManager = o.strManager,
                strWaterSeasonName =season.strName,
                strFeeStardName =fee.strName ,
               decUseArea =u.UseArea.Value  ,
               decWaterBill =u.WaterBill.Value ,
               decWaterCap =u.WaterCap.Value  ,
               decLastArrears =0.0M,
               decReceivedBill =u.WaterBill.Value  ,
               decThisArrears =0.0M

            };


           return orgSql ;
        } 


        private void cmbGroup_SelectedIndexChanged(object sender, EventArgs e)
        {
    
        }

        private void cmbWaterSeason_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.btnQuery.Enabled = true;
        }

        private void WaterBillReportForm_Load(object sender, EventArgs e)
        {

        }

        private void tableLayoutPanel1_Paint(object sender, PaintEventArgs e)
        {

        }

    }
}
